﻿Imports System.Data.SqlClient
Public Class foodlist
    Dim cmd As New SqlCommand
#Region "Select and refresh Database"
    Public Sub refreshdata()
        grdfoodlist.Rows.Clear()
        cmd.Connection = cn
        cmd.CommandText = "select * from tblproduct"
        Dim rd As SqlDataReader = cmd.ExecuteReader
        While rd.Read = True
            Me.grdfoodlist.Rows.Add(rd(0), rd(1), rd(2), rd(3), rd(4))
        End While
        rd.Close()
    End Sub
#End Region

#Region "Connection"

    Private Sub foodlist_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        condb()
        refreshdata()
        Main.lblheader.Text = "បញ្ជីមុខម្ហូប និងតម្លៃ"
    End Sub
#End Region

#Region " Code for Search"
    Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
        grdfoodlist.Rows.Clear()
        cmd.Connection = cn
        cmd.CommandText = "select * from tblproduct where ProductName like N'%" & txtsearch.Text & "%' OR Product_Code like N'%" & txtsearch.Text & "%'"
        Dim rd As SqlDataReader = cmd.ExecuteReader
        While rd.Read = True
            Me.grdfoodlist.Rows.Add(rd(0), rd(1), rd(2), rd(3), rd(4))
        End While
        rd.Close()
    End Sub
#End Region

#Region "Insert Data to Database"
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        'Try
        'cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "INSERT INTO tblproduct VALUES (N'" & txtproductname.Text & "',N'" & txtprice.Text & "',N'" & txtphoto.Text & "',N'" & txtcategoryname.SelectedValue & "',N'" & txtdescription.Text & "')"
        cmd.ExecuteNonQuery()
        refreshdata()
        'Catch ex As Exception

        'End Try
    End Sub
#End Region

#Region "Update"
    Private Sub grdfoodlist_SelectionChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles grdfoodlist.SelectionChanged
        Try
            If grdfoodlist.SelectedRows.Count = 0 Then
                Exit Sub
            End If
            Me.PictureBox1.Image = Nothing
            Dim row As DataGridViewRow = Me.grdfoodlist.SelectedRows(0)
            Me.txtid.Text = row.Cells("code").Value
            Me.txtproductname.Text = row.Cells("Proname").Value
            Me.txtprice.Text = row.Cells("price").Value
            Me.PictureBox1.Image = Image.FromFile(row.Cells("photo").Value)
            Me.txtdescription.Text = row.Cells("discription").Value
            Me.txtcategoryname.SelectedValue = row.Cells("categoryname").Value
            PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            PictureBox1.BorderStyle = BorderStyle.Fixed3D

        Catch ex As Exception
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = "update Tblproduct set ProductName=N'" & txtproductname.Text & "',Price=N'" & txtprice.Text & "',Photo=N'" & txtphoto.Text & "',CategoryName=N'" & txtcategoryname.SelectedValue & "',Description=N'" & txtdescription.Text & "' where Product_Code='" & txtid.Text & "';"
        cmd.ExecuteNonQuery()
        refreshdata()


    End Sub
#End Region

#Region "Clear Data get out from TexBox"
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        txtdescription.Clear()
        txtid.Clear()
        txtphoto.Clear()
        txtprice.Clear()
        txtproductname.Clear()
        txtid.Clear()
    End Sub
#End Region

#Region "Brow to get photo"
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        With OpenFileDialog1
            .Filter = "All Files|*.*|Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg|PNG|*.PNG"
            .FilterIndex = 4
        End With
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
            PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            PictureBox1.BorderStyle = BorderStyle.Fixed3D
            PictureBox1.Text = OpenFileDialog1.FileName()
            txtphoto.Text = OpenFileDialog1.FileName
        End If
    End Sub
#End Region


    Private Sub txtproductname_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtproductname.KeyDown
        If e.KeyCode = Keys.Enter Then
            Button4_Click(sender, e)
        End If
    End Sub
#Region "Delet Data from database"

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            If grdfoodlist.SelectedRows.Count = 0 Then
                Exit Sub
            End If
            If MessageBox.Show("Are you want to delete", "Delete Confirme", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
            For Each row In grdfoodlist.SelectedRows
                Dim id = row.Cells("code").Value
                cmd.Connection = cn
                cmd.CommandText = "Delete tblproduct where Product_Code=" & id
                cmd.ExecuteNonQuery()
            Next
            refreshdata()
        Catch ex As Exception
        End Try

    End Sub
#End Region


    Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Category.ShowDialog()
    End Sub

    Private Sub txtgender_MouseHover(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtcategoryname.MouseHover
        cmd.Connection = cn
        cmd.CommandText = "select CategoryName from tblCategory"
        Dim reader = cmd.ExecuteReader
        Dim dt As New DataTable
        dt.Load(reader)
        reader.Close()
        Me.txtcategoryname.ValueMember = "CategoryName"
        Me.txtcategoryname.DisplayMember = "CategoryName"
        Me.txtcategoryname.DataSource = dt
    End Sub

    Private Sub grdfoodlist_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdfoodlist.CellContentClick

    End Sub
End Class